package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.queryParams.HospitalQueryParams;
import com.css.fxfzypg.modules.baseData.repository.HospitalRepository;
import com.css.fxfzypg.modules.baseData.entity.HospitalEntity;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-19 10:20
 */
@Repository
public class HospitalRepositoryImpl implements HospitalRepository {


    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public HospitalEntity saveHospital(HospitalEntity hospitalEntity) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                String sql = "";
                sql = "insert into data_hospital (dh_longitude,dh_latitude,dh_name,dh_type,dh_power,dh_bed_num,dh_address,dh_createtime,dh_isdelete) values(?,?,?,?,?,?,?,?,?)";
                //  sql="insert into data_school values(null,?,?,?,?,?,?,?,null ,?)";

                //String sql_sms = "insert into  sms(title,content,date_s,form,sffs,by1,by2,by3) values (?,?,'"+dates+"',?,?,?,?,?)";
                PreparedStatement ps = connection.prepareStatement(sql, new String[]{"dh_id"});
                ps.setFloat(1, hospitalEntity.getDhLongitude());
                ps.setFloat(2, hospitalEntity.getDhLatitude());
                ps.setString(3, hospitalEntity.getDhName());
                ps.setString(4, hospitalEntity.getDhType());
                ps.setInt(5, hospitalEntity.getDhPower());
                ps.setInt(6, hospitalEntity.getDhBedNum());
                ps.setString(7, hospitalEntity.getDhAddress());
                java.sql.Date createDate = new java.sql.Date(hospitalEntity.getDhCreatetime().getTime());
                ps.setDate(8, createDate);
                ps.setString(9, hospitalEntity.getDhIsdelete());
                return ps;
            }
        }, keyHolder);

        int i = keyHolder.getKey().intValue();
        hospitalEntity.setDhId(i);
        return hospitalEntity;
    }

    @Override
    public HospitalEntity findHospitalById(Integer dataHospitalId) {
        String sql = "";
        sql = "select  u.* from data_hospital u where dh_Id = '" + dataHospitalId + "'";

        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(HospitalEntity.class));
    }

    @Override
    public Page<HospitalEntity> getAllHospitalByParams(HospitalQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  u.* from data_hospital u ");

        //  StringBuilder orderSql = new StringBuilder(" order by u.sort");

        StringBuilder whereSql = new StringBuilder(" where u.dh_isdelete = '" + YNEnum.Y.toString() + "'");

        if (!PlatformObjectUtils.isEmpty(queryParams.getDhLongitude())) {
            whereSql.append(" and u.dh_longitude like %" + queryParams.getDhLongitude() + "%");
        }

        if (!PlatformObjectUtils.isEmpty(queryParams.getDhLatitude())) {
            whereSql.append(" and u.dh_latitude like %" + queryParams.getDhLatitude() + "%");
        }

        String wholeSql = sql.append(whereSql).toString();
        List<HospitalEntity> list = jdbcTemplate.query(wholeSql, new BeanPropertyRowMapper<>(HospitalEntity.class));
        Pageable pageable = PageRequest.of(queryParams.getCurPage() - 1, queryParams.getPageSize());
        Page<HospitalEntity> hospitalEntities = new PageImpl<>(list, pageable, PlatformObjectUtils.isEmpty(list) ? 0 : list.size());
        return hospitalEntities;

    }

    @Override
    public void deleteHospital(List<Integer> ids, Timestamp currentTimestamp) {
        String sql = "update data_hospital set dh_isdelete = '" + YNEnum.N.toString() + "' ,dh_updatetime ='" + currentTimestamp + "' " +
                " where dh_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<HospitalEntity> exportExcel() {
        return jdbcTemplate.query("select u.* from data_hospital u where u.dh_isdelete='" + YNEnum.Y.toString() + "' ", new BeanPropertyRowMapper<>(HospitalEntity.class));

    }

    @Override
    public void importExcel(List<HospitalEntity> hospitalEntities) {
        jdbcTemplate.batchUpdate("insert into data_hospital (dh_longitude,dh_latitude,dh_name,dh_type,dh_power,dh_bed_num,dh_address,dh_createtime,dh_isdelete)values(?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setFloat(1, hospitalEntities.get(i).getDhLongitude());
                ps.setFloat(2, hospitalEntities.get(i).getDhLatitude());
                ps.setString(3, hospitalEntities.get(i).getDhName());
                ps.setString(4, hospitalEntities.get(i).getDhType());
                ps.setInt(5, hospitalEntities.get(i).getDhPower());
                ps.setFloat(6, hospitalEntities.get(i).getDhBedNum());
                ps.setString(7, hospitalEntities.get(i).getDhAddress());
                java.sql.Date createDate = new java.sql.Date(hospitalEntities.get(i).getDhCreatetime().getTime());
                ps.setDate(8, createDate);
                ps.setString(9, hospitalEntities.get(i).getDhIsdelete());
            }

            @Override
            public int getBatchSize() {
                return hospitalEntities.size();
            }
        });
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_hospital where county = '" + county + "' and city = '" + city + "' and province = '" + province + "' or (province ='"+province+"' and city ='' and county ='') and dh_isdelete ='"+YNEnum.Y+"'", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_hospital where city = '" + city + "' and province = '" + province + "'  and dh_isdelete ='"+YNEnum.Y+"'  ", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_hospital where province = '" + province + "' and dh_isdelete ='"+YNEnum.Y+"'   ", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_hospital where province = ? and (TO_CHAR(dh_createtime, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(dh_updatetime, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and dh_isdelete = '1'", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
